
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[spShippingStatusReportGetListBySortType]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [spShippingStatusReportGetListBySortType]
GO

CREATE PROCEDURE spShippingStatusReportGetListBySortType
	@OfficeID int,
	@SortType int,	
	@poType int,	
	@SalesOrder int,
	@Page int,
	@PageSize int,
	@TotalRecords int output
AS

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

CREATE TABLE #TempShippingStatusReport (
	RowNumber INT IDENTITY (1, 1) NOT NULL,
	ID int	
)

DECLARE @sql nvarchar(2000)

-- insert primary keys into temp table
SET @sql =	N'INSERT INTO #TempShippingStatusReport ([ID]) SELECT '
IF (@PageSize > 0)
	SET @sql = @sql + ' TOP ' + CAST(@Page*@PageSize as nvarchar) 

SET @sql = @sql + ' [ID] FROM [ShippingStatusReport] LEFT JOIN Suppliers ON [ShippingStatusReport].PONo_Supplier = Suppliers.SupplierCode ' 
		+ ' LEFT JOIN Conpoints ON Conpoints.ConpointID = Suppliers.ConpointID '
		+ ' WHERE OfficeID=' + CAST(@OfficeID as nvarchar) 
		+ ' AND (' + CAST(@SalesOrder as nvarchar) + '=-1 OR ' + CAST(@SalesOrder as nvarchar)+ ' = SalesOrder)'
		+ ' AND ((' + CAST(@poType as nvarchar) + '=0 AND CompletedDate IS NULL AND [ShippingStatusReport].IsArchived=0) 
				OR (' + CAST(@poType as nvarchar) + '=1 AND CompletedDate IS NOT NULL AND [ShippingStatusReport].IsArchived=0 )
				OR (' + CAST(@poType as nvarchar) + '=2 AND [ShippingStatusReport].IsArchived=1 )) '
IF @SortType = 0 
	SET @sql = @sql + ' ORDER BY REQ ASC, IsPrimary ASC '
ELSE IF @SortType = 1 
	SET @sql = @sql + ' ORDER BY PONo_Type ASC, REQ ASC, IsPrimary, PONo_Supplier '
ELSE IF @SortType = 2 
	SET @sql = @sql + ' ORDER BY FactoryETD, REQ, IsPrimary ASC '
ELSE IF @SortType = 3 
	SET @sql = @sql + ' ORDER BY CustomerOrLocation, CustPONo, FinalDestination, REQ, IsPrimary ASC '
ELSE IF @SortType = 4 
	SET @sql = @sql + ' ORDER BY ETA, REQ, IsPrimary ASC '
ELSE IF @SortType = 5 
	SET @sql = @sql + ' ORDER BY PONo_Supplier, PONo_Number '
ELSE IF @SortType = 6 
	SET @sql = @sql + ' ORDER BY ContainerDeliveried, REQ, IsPrimary ASC '
ELSE IF @SortType = 7 
	SET @sql = @sql + ' ORDER BY CustomerOrLocation, [PI], REQ, IsPrimary ASC '
ELSE IF @SortType = 8 
	SET @sql = @sql + ' ORDER BY Conpoints.Description, REQ, IsPrimary ASC '
ELSE IF @SortType = 9 
	SET @sql = @sql + ' ORDER BY SalesOrder, REQ, IsPrimary ASC '	
ELSE IF @SortType = 10
	SET @sql = @sql + ' ORDER BY [SInvoiced], REQ, IsPrimary ASC '	
ELSE IF @SortType = 11
	SET @sql = @sql + ' ORDER BY [CustomerOrLocation], [SInvoiced], REQ, IsPrimary ASC '	

EXEC (@sql)

SELECT @TotalRecords = COUNT(*) FROM [ShippingStatusReport] 
WHERE OfficeID=@OfficeID
AND (@SalesOrder = -1 OR SalesOrder = @SalesORder)
AND (
	   (@poType = 0 AND CompletedDate IS NULL AND IsArchived=0) 
	OR (@poType = 1 AND CompletedDate IS NOT NULL AND IsArchived=0)
	OR (@poType = 2 AND IsArchived=1 )
	)

SELECT
	[ShippingStatusReport].[ID],
	[REQ],
	[OrderDate],
	[ContainerSize],
	[LP],
	[PONo_Office],
	[PONo_Type],
	[PONo_Supplier],
	[PONo_Number],
	[IsPrimary],
	[PONo_Consolidation],
	[AccNo],
	[CustomerOrLocation],
	[FinalDestination],
	[ShippingStatusReport].[Description],
	[FactoryETD],
	[ShippingDate],
	[ShippingLine].Abbr as ShippingLine,
	[Vessel],
	[ContainerNo],
	[ETA],
	[Week],
	[ArrivalPort],
	[PI],
	[Payment],
	[DocReceived],
	[CompletedDate],
	[BulkType],
	[ModifiedDate],
	[BulkOrderNo],
	[PayAuth],
	[ContainerDeliveried],
	[POInOpera],
	[InvValue],
	[PayDate],
	[Remarks],
	[SInvoiced],
	[SuppOrderNo],
	[OnHold],
	[OfficeID],
	[CustPONo],
	[BulkOrderOfficeID],
	getdate() CurrentDate,
	[SInvoicedPd],
	(SELECT ConPoints.[Description] FROM ConPoints
		WHERE ConPoints.ConPointID = Suppliers.ConPointID) ConPoint,
	[ShippingLineID],
	[PDepValue],
	[SalesOrder],
	[SalesInvoiceValue],
	[SalesInvoiceDate],
	[SalesPaymentDue],
	[TargetETD],
	[ShippingStatusReport].[IsArchived],
	[ResponsibleID],
	(SELECT [Name] FROM Responsibles 
		WHERE Responsibles.[ResponsibleID] = [ShippingStatusReport].[ResponsibleID]) Responsible,
	CASE 
		WHEN [FactoryETDEnteredDate] IS NULL THEN NULL
		ELSE DATEDIFF(day, OrderDate, [FactoryETDEnteredDate]) 
	END AS LagDays		
FROM
	#TempShippingStatusReport AS tblTemp JOIN [ShippingStatusReport] ON
	tblTemp.ID = [ShippingStatusReport].ID 	
	LEFT JOIN ShippingLine ON [ShippingStatusReport].ShippingLineID = ShippingLine.ID
	LEFT JOIN Suppliers ON [ShippingStatusReport].PONo_Supplier = Suppliers.SupplierCode
	
WHERE (@PageSize = 0) OR (@PageSize > 0 AND (@Page - 1)*@PageSize < RowNumber AND RowNumber <= @Page*@PageSize)
ORDER BY RowNumber

DROP TABLE #TempShippingStatusReport

GO

	
 